﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// 只执行存储过程！存储过程返回值和返回参数可通过执行后的parameter对象获得。
/// 每个查询都重新建立链接，可靠，但效率稍差。系统连接池应该可以加快反复重新链接的速度。
/// Exception 由外部负责捕获处理
/// </summary>
public class TinyDAL
{
    //Data Source=127.0.0.1,1433;Network Library=DBMSSOCN;Initial Catalog=sparrow;User ID=sa;Password=;Packet Size=4096;
    //uid=sa;pwd=;Initial Catalog=sparrow;server=127.0.0.1

    //private const string sConn = "Data Source=127.0.0.1,1433;Network Library=DBMSSOCN;Database=sparrow;User ID=sa;Password=;Packet Size=4096;Max Pool Size=10";
    //private const string sConn = "Data Source=localhost\\sqlexpress;Initial Catalog=sparrow;Integrated Security=True";

    //private string sConn;
    //public static string sDomain = "localhost/sparrow";
    public TinyDAL()
	{
        //sConn = WebConfigurationManager.ConnectionStrings["IFD_DB"].ConnectionString;
        //sConn = System.Configuration.ConfigurationSettings.AppSettings["ConnString"];
	}

    public static int Exec(ref SqlCommand cmd, ref DataTable dt)//返回一个DataTable
    {
        int ret_value;
        string sConn;
        //try
        //{
            sConn = WebConfigurationManager.ConnectionStrings["IFD_DB"].ConnectionString;
        //}
        //catch (Exception ex)
        //{
        //    return -1;
        //}
        SqlParameter para = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.SmallInt);
        para.Direction = ParameterDirection.ReturnValue;
        SqlDataAdapter adapter = new SqlDataAdapter();
        using (SqlConnection conn = new SqlConnection(sConn))
        {
            //try
            //{
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                dt.Clear();
                conn.Open();
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);
                ret_value =Convert.ToInt32(para.Value);
                conn.Close();
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
        }
        return ret_value;
    }

    public static int Exec(ref SqlCommand cmd, ref DataSet ds)//返回一个DataSet。just for ajax webservice testing,erase it later!
    {
        int ret_value;
        string sConn;
        //try
        //{
        sConn = WebConfigurationManager.ConnectionStrings["IFD_DB"].ConnectionString;
        //}
        //catch (Exception ex)
        //{
        //    return -1;
        //}
        SqlParameter para = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.SmallInt);
        para.Direction = ParameterDirection.ReturnValue;
        SqlDataAdapter adapter = new SqlDataAdapter();
        using (SqlConnection conn = new SqlConnection(sConn))
        {
            //try
            //{
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            ds.Clear();
            conn.Open();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
            ret_value = Convert.ToInt32(para.Value);
            conn.Close();
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
        }
        return ret_value;
    }

    public static int Exec(ref SqlCommand cmd)//不返回结果集，只执行一条SQL语句
    {
        int ret_value;
        string sConn;
        //try
        //{
        sConn = WebConfigurationManager.ConnectionStrings["IFD_DB"].ConnectionString;
        //}
        //catch (Exception ex)
        //{
        //    return -1;
        //}
        SqlParameter para = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.SmallInt);
        para.Direction = ParameterDirection.ReturnValue;
        using (SqlConnection conn = new SqlConnection(sConn))
        {
            //try
            //{
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                cmd.ExecuteNonQuery();
                ret_value = Convert.ToInt32(para.Value);
                conn.Close();
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
        }
        return ret_value;
    }

    //public static string GetRequestDomain(string original_string)
    //{
    //    string url_tmp = original_string;
    //    string url;
    //    int idx;
    //    idx = url_tmp.LastIndexOf('/');
    //    if (idx > 1)
    //        url = url_tmp.Substring(0, idx);
    //    else
    //    {
    //        //Response.Redirect("Invalid query string!");
    //        return "";
    //    }
    //    url_tmp = url;
    //    idx = url_tmp.IndexOf(':');
    //    if (idx > 0)
    //        url = url_tmp.Substring(idx, url.Length - idx);
    //    else
    //    {
    //        //Response.Redirect("Invalid query string!");
    //        return "";
    //    }
    //    return url;
    //}

    public static string Get_DB_UTC_Time()//返回一个DataTable
    {
        string sConn;
        SqlCommand cmd = new SqlCommand();
        DataTable dt=new DataTable();
        //try
        //{
        sConn = WebConfigurationManager.ConnectionStrings["IFD_DB"].ConnectionString;
        //}
        //catch (Exception ex)
        //{
        //    return -1;
        //}
       
        SqlDataAdapter adapter = new SqlDataAdapter();
        using (SqlConnection conn = new SqlConnection(sConn))
        {
            try
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select getutcdate()";
                conn.Open();
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);
                if (dt.Rows.Count == 1)
                {
                    return Convert.ToDateTime(dt.Rows[0][0]).ToString("yyyy/MM/dd HH:mm:ss");
                }
                else
                {
                    return "";
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                return "";//return ex.Message;
            }
        }
    }
}
